﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;

namespace Project_ScansiaPacific
{
    public partial class FrTKReport : DevComponents.DotNetBar.Office2007Form
    {
        public FrTKReport()
        {
            InitializeComponent();
        }
        private string MaSP1, MaChiTiet1, Ngay_Start, Ngay_End,child=" - ";
        private void combo_load()
        {
            Data dta = new Data();
            FormProcess Frp = new FormProcess();
            
            string[] arr = new string[] { "KV1", "KV2","KV2-AQL", "KV3", "KV4","KV5_DG","KV5_NK" };
            for (int i = 0; i < arr.Length; i++)
            {
                cb_Chot.Items.Add(arr[i].ToString());
            }
            string[] arr_NhomLoi = new string[] { "Bề Mặt", "Gỗ KT", "Gia Công","Tất Cả"};
            string sql_load = @"select MaSP,TenSP from dbo.SanPham";
            Frp.cb1(cb_MaSP, sql_load, 1, 0);
            string[] arr_Loi = new string[] { "Bể Cạnh - 01", "Đường Cưa-Cấn Roulo - 02", "Lốc Xước - 03", "Nứt-Tét Đầu - 04", "Mẻ Đầu Nan - 05", "Lẹm - 06", "Đầu Nan Sần Sùi - 07", "Chà Nhám Ko Sạch - 08", "Trám Trét Ko Đầy - 09", "Định Vị Sai KT - 10", "Đường Vát Bo Đầu Ko Đạt - 11", "Đường Ghép - 12", "Gỗ KT - 13", "Keo Ráp Dính - 14", "Siết Ốc Ko Đạt - 15", "Nút Nhựa Ko Sát - 16", "Cấn Móp - 17", "Khe Nan - 18", "Chảy Dầu - 19", "Trầy Dầu - 20", "Thiếu Dầu - 21", "Cặn Dầu - 22", "Màu Ko Đều - 23", "Đường Ghép Hở - 24", "Mắt Ko Đúng - 25", "Cong Vặn - 26", "Đường Ruột - 27", "Mục Mọc - 28", "Giác - 29", "Keo Ghép Dư - 30", "Thiếu Quy Cách - 31", "Độ Ẩm - 32", "Mắt Sống Ko Đạt - 33", "Định Vị Sai Mặt & Vị Trí - 34", "Đường Vát Bo Cạnh Ko Đạt - 35", "Hở Mộng - 36", "Nan Sai Mặt - 37", "Nan Sai Tiêu Chuẩn - 38", "Cảo Tét - 39", "Nan Cao Thấp - 40", "Ba Chân - 41", "Tán Không Đạt - 42", "Sai Màu - 43", "Đọng Dầu - 44", "Lem Dầu - 45", "Răng Nứt - 46", "Khác - 47"};
            for (int j = 0; j < arr_Loi.Length; j++)
            {
                cb_LoiRp.Items.Add(arr_Loi[j].ToString());
            }
            /*sql_load = @"select MaChiTiet,TenChiTiet from dbo.ChiTietSP";
            Frp.cb1(cb_ChiTiet, sql_load, 0,1);*/
        }
        /*private void ExportExcel(DataTable dt,string sheetName,string title)
        {
            Microsoft.Office.Interop.Excel.Application oExcel = new Microsoft.Office.Interop.Excel.Application();
            Microsoft.Office.Interop.Excel.Workbooks oBooks;
            Microsoft.Office.Interop.Excel.Sheets oSheets;
            Microsoft.Office.Interop.Excel.Workbook oBook;
            Microsoft.Office.Interop.Excel.Worksheet oSheet;
            oExcel.Visible = true;
            oExcel.DisplayAlerts = false;
            oExcel.Application.SheetsInNewWorkbook = 1;
            oBooks = oExcel.Workbooks;
            oBook = (Microsoft.Office.Interop.Excel.Workbook)(oExcel.Workbooks.Add(Type.Missing));
            oSheets = oBook.Worksheets;
            oSheet = (Microsoft.Office.Interop.Excel.Worksheet)oSheets.get_Item(1);
            oSheet.Name = sheetName;
            //Tạo phần tiêu đề
            Microsoft.Office.Interop.Excel.Range head = oSheet.get_Range("B1", "I1");
            head.MergeCells = true;
            head.Value2 = title;
            head.Font.Bold = true;
            head.Font.Name = "Times new Roman";
            head.Font.Size = "18";
            head.RowHeight = 55;
            head.WrapText = true;
            head.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
            //object[,] arr = new object[dt.Rows.Count, dt.Columns.Count];
            //Tạo Tiêu đề cho cột

            Microsoft.Office.Interop.Excel.Range cl1 = oSheet.get_Range("A15", "A15");
            cl1.Value2 = "Bể Cạnh";
            cl1.ColumnWidth = 24;
            Microsoft.Office.Interop.Excel.Range cl2 = oSheet.get_Range("A17", "A17");
            cl2.Value2 = "Đường Cưa, Cấn Roulo";
            Microsoft.Office.Interop.Excel.Range cl3 = oSheet.get_Range("A19", "A19");
            cl3.Value2 = "Lốc Xước";
            Microsoft.Office.Interop.Excel.Range cl4 = oSheet.get_Range("A21", "A21");
            cl4.Value2 = "Nứt, Tét Đầu";
            Microsoft.Office.Interop.Excel.Range cl5 = oSheet.get_Range("A23", "A23");
            cl5.Value2 = "Mẻ Đầu Nan";
            Microsoft.Office.Interop.Excel.Range cl6 = oSheet.get_Range("A25", "A25");
            cl6.Value2 = "Lẹm";
            Microsoft.Office.Interop.Excel.Range cl7 = oSheet.get_Range("A27", "A27");
            cl7.Value2 = "Đầu Nan Sần Sùi";
            Microsoft.Office.Interop.Excel.Range cl8 = oSheet.get_Range("A29", "A29");
            cl8.Value2 = "Chà Nhám Không Sạch";
            Microsoft.Office.Interop.Excel.Range cl9 = oSheet.get_Range("A31", "A31");
            cl9.Value2 = "Trám Trét Không Đầy";
            Microsoft.Office.Interop.Excel.Range cl10 = oSheet.get_Range("A33", "A33");
            cl10.Value2 = "Định Vị Sai";
            Microsoft.Office.Interop.Excel.Range cl11 = oSheet.get_Range("A35", "A35");
            cl11.Value2 = "Đường Vát Không Đạt";
            Microsoft.Office.Interop.Excel.Range cl12 = oSheet.get_Range("A37", "A37");
            cl12.Value2 = "Đường Ghép";
            Microsoft.Office.Interop.Excel.Range cl13 = oSheet.get_Range("A39", "A39");
            cl13.Value2 = "Gỗ";
            Microsoft.Office.Interop.Excel.Range cl14 = oSheet.get_Range("A41", "A41");
            cl14.Value2 = "Keo";
            Microsoft.Office.Interop.Excel.Range cl15 = oSheet.get_Range("A43", "A43");
            cl15.Value2 = "Siết Ốc Không Chặt";
            Microsoft.Office.Interop.Excel.Range cl16 = oSheet.get_Range("A45", "A45");
            cl16.Value2 = "Đóng Nút Nhựa Không Sát";
            Microsoft.Office.Interop.Excel.Range cl17 = oSheet.get_Range("A47", "A47");
            cl17.Value2 = "Cấn Móp";
            Microsoft.Office.Interop.Excel.Range cl18 = oSheet.get_Range("A49", "A49");
            cl18.Value2 = "Khe Nan Không Đều";
            Microsoft.Office.Interop.Excel.Range cl19 = oSheet.get_Range("A51", "A51");
            cl19.Value2 = "Chảy Dầu";
            Microsoft.Office.Interop.Excel.Range cl20 = oSheet.get_Range("A53", "A53");
            cl20.Value2 = "Trầy Dầu";
            Microsoft.Office.Interop.Excel.Range cl21 = oSheet.get_Range("A55", "A55");
            cl21.Value2 = "Thiếu Dầu";
            Microsoft.Office.Interop.Excel.Range cl22 = oSheet.get_Range("A57", "A57");
            cl22.Value2 = "Cặn Dầu";
            Microsoft.Office.Interop.Excel.Range cl23 = oSheet.get_Range("A59", "A59");
            cl23.Value2 = "Màu Không Đều";
            Microsoft.Office.Interop.Excel.Range cl24 = oSheet.get_Range("A9", "A9");
            cl24.Value2 = "SL Trên Phiếu";
            Microsoft.Office.Interop.Excel.Range cl25 = oSheet.get_Range("A10", "A10");
            cl25.Value2 = "SL Thực Tế";
            Microsoft.Office.Interop.Excel.Range cl26 = oSheet.get_Range("A11", "A11");
            cl26.Value2 = "SL Lấy Mẫu";
            Microsoft.Office.Interop.Excel.Range cl27 = oSheet.get_Range("A12", "A12");
            cl27.Value2 = "SL Không Đạt";
            Microsoft.Office.Interop.Excel.Range cl28 = oSheet.get_Range("A14", "A14");
            cl28.Value2 = "Khu Vực";
            Microsoft.Office.Interop.Excel.Range cl29 = oSheet.get_Range("A6", "A6");
            cl29.Value2 = "Ngày";
             Microsoft.Office.Interop.Excel.Range cl30 = oSheet.get_Range("A7", "A7");
             cl30.Value2 = "Tên SP";
            Microsoft.Office.Interop.Excel.Range cl31 = oSheet.get_Range("A8", "A8");
            cl31.Value2 = "Chi Tiết";
            object[,] arr = new object[dt.Columns.Count,dt.Rows.Count];
            //Chuyển dữ liệu từ DataTable vào mảng đối tượng
            for (int c = 0; c < dt.Columns.Count; c++)
            {
                //DataColumn dr = dt.Columns[r];
                for (int r = 0; r < dt.Rows.Count; r++)
                {
                    DataRow dr = dt.Rows[r];
                    arr[c, r] = dr[c];
                }
            }

            //Thiết lập vùng điền dữ liệu
            int rowStart = 6;
            int columnStart = 2;

            //int rowEnd = rowStart + dt.Rows.Count - 1;
            int rowEnd = rowStart + dt.Columns.Count-1;

            //int columnEnd = dt.Columns.Count;
            int columnEnd = dt.Rows.Count-1+columnStart;

            // Ô bắt đầu điền dữ liệu
            Microsoft.Office.Interop.Excel.Range c1 = (Microsoft.Office.Interop.Excel.Range)oSheet.Cells[rowStart, columnStart];
            // Ô kết thúc điền dữ liệu
            Microsoft.Office.Interop.Excel.Range c2 = (Microsoft.Office.Interop.Excel.Range)oSheet.Cells[rowEnd, columnEnd];
            // Lấy về vùng điền dữ liệu
            Microsoft.Office.Interop.Excel.Range range = oSheet.get_Range(c1, c2);

            //Điền dữ liệu vào vùng đã thiết lập
            range.Value2 = arr;
            range.Borders.LineStyle = Microsoft.Office.Interop.Excel.Constants.xlSolid;
            


        }*/
        private void ExportExcel(DataTable dt,string sheetName)
       {
            
           Microsoft.Office.Interop.Excel.Application ExcelObj = new Microsoft.Office.Interop.Excel.Application();
           ExcelObj.Visible = true;
           Microsoft.Office.Interop.Excel.Workbook theWorkbook = ExcelObj.Workbooks.Open(@"C:\\Huynh\\LoiTongThe.xlsx", 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, false, Microsoft.Office.Interop.Excel.XlCorruptLoad.xlNormalLoad);
           Microsoft.Office.Interop.Excel.Sheets sheet = theWorkbook.Worksheets;
           Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)sheet.get_Item(1);
           //worksheet.Activate();
           object[,] arr = new object[dt.Columns.Count,dt.Rows.Count];
           //Chuyển dữ liệu từ DataTable vào mảng đối tượng
           for (int c = 0; c < dt.Columns.Count; c++)
           {
               //DataColumn dr = dt.Columns[r];
               for (int r = 0; r < dt.Rows.Count; r++)
               {
                   DataRow dr = dt.Rows[r];
                   arr[c, r] = dr[c];
               }
           }

           //Thiết lập vùng điền dữ liệu
           int rowStart = 6;
           int columnStart = 2;

           //int rowEnd = rowStart + dt.Rows.Count - 1;
           int rowEnd = rowStart + dt.Columns.Count-1;

           //int columnEnd = dt.Columns.Count;
           int columnEnd = dt.Rows.Count-1+columnStart;

           // Ô bắt đầu điền dữ liệu
           Microsoft.Office.Interop.Excel.Range c1 = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[rowStart, columnStart];
           // Ô kết thúc điền dữ liệu
           Microsoft.Office.Interop.Excel.Range c2 = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[rowEnd, columnEnd];
           // Lấy về vùng điền dữ liệu
           Microsoft.Office.Interop.Excel.Range range = worksheet.get_Range(c1, c2);

           //Điền dữ liệu vào vùng đã thiết lập
           range.Value2 = arr;
           //range.Columns.AutoFit();
           range.Borders.LineStyle = Microsoft.Office.Interop.Excel.Constants.xlSolid;
            //theWorkbook=theWorkbook.Open("C:\\test.xlsx");
           //worksheet = ("sheet1");
           //range.Activate();
           object misValue = System.Reflection.Missing.Value;
           theWorkbook.Close(true, misValue, misValue);
           ExcelObj.Quit();

       }
        private void ExportExcel_ThoiGian(DataTable dt, string sheetName)
        {

            Microsoft.Office.Interop.Excel.Application ExcelObj = new Microsoft.Office.Interop.Excel.Application();
            ExcelObj.Visible = true;
            Microsoft.Office.Interop.Excel.Workbook theWorkbook = ExcelObj.Workbooks.Open(@"C:\\Huynh\\TKTG.xlsx", 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, false, Microsoft.Office.Interop.Excel.XlCorruptLoad.xlNormalLoad);
            Microsoft.Office.Interop.Excel.Sheets sheet = theWorkbook.Worksheets;
            Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)sheet.get_Item(1);
            //worksheet.Activate();
            object[,] arr = new object[dt.Columns.Count, dt.Rows.Count];
            //Chuyển dữ liệu từ DataTable vào mảng đối tượng
            for (int c = 0; c < dt.Columns.Count; c++)
            {
                //DataColumn dr = dt.Columns[r];
                for (int r = 0; r < dt.Rows.Count; r++)
                {
                    DataRow dr = dt.Rows[r];
                    arr[c, r] = dr[c];
                }
            }

            //Thiết lập vùng điền dữ liệu
            int rowStart = 6;
            int columnStart = 2;

            //int rowEnd = rowStart + dt.Rows.Count - 1;
            int rowEnd = rowStart + dt.Columns.Count - 1;

            //int columnEnd = dt.Columns.Count;
            int columnEnd = dt.Rows.Count - 1 + columnStart;

            // Ô bắt đầu điền dữ liệu
            Microsoft.Office.Interop.Excel.Range c1 = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[rowStart, columnStart];
            // Ô kết thúc điền dữ liệu
            Microsoft.Office.Interop.Excel.Range c2 = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[rowEnd, columnEnd];
            // Lấy về vùng điền dữ liệu
            Microsoft.Office.Interop.Excel.Range range = worksheet.get_Range(c1, c2);

            //Điền dữ liệu vào vùng đã thiết lập
            range.Value2 = arr;
            //range.Columns.AutoFit();//Điều chỉnh cho phù hợp với ô Dữ Liệu
            range.Borders.LineStyle = Microsoft.Office.Interop.Excel.Constants.xlSolid;
            //theWorkbook=theWorkbook.Open("C:\\Huynh\\test.xlsx");
            //worksheet = ("sheet1");
            //range.Activate();
            object misValue = System.Reflection.Missing.Value;
            theWorkbook.Close(true, misValue, misValue);
            ExcelObj.Quit();

        }
        private void ExportExcel_NhomLoi(DataTable dt, string sheetName)
        {
            Microsoft.Office.Interop.Excel.Application ExcelObj = new Microsoft.Office.Interop.Excel.Application();
            ExcelObj.Visible = true;
            Microsoft.Office.Interop.Excel.Workbook theWorkbook = ExcelObj.Workbooks.Open(@"C:\\Huynh\\NhomLoi.xlsx", 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, false, Microsoft.Office.Interop.Excel.XlCorruptLoad.xlNormalLoad);
            Microsoft.Office.Interop.Excel.Sheets sheet = theWorkbook.Worksheets;
            Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)sheet.get_Item(1);
            //worksheet.Activate();
            object[,] arr = new object[dt.Columns.Count, dt.Rows.Count];
            //Chuyển dữ liệu từ DataTable vào mảng đối tượng
            for (int c = 0; c < dt.Columns.Count; c++)
            {
                //DataColumn dr = dt.Columns[r];
                for (int r = 0; r < dt.Rows.Count; r++)
                {
                    DataRow dr = dt.Rows[r];
                    arr[c, r] = dr[c];
                }
            }

            //Thiết lập vùng điền dữ liệu
            int rowStart = 6;
            int columnStart = 2;

            //int rowEnd = rowStart + dt.Rows.Count - 1;
            int rowEnd = rowStart + dt.Columns.Count - 1;

            //int columnEnd = dt.Columns.Count;
            int columnEnd = dt.Rows.Count - 1 + columnStart;

            // Ô bắt đầu điền dữ liệu
            Microsoft.Office.Interop.Excel.Range c1 = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[rowStart, columnStart];
            // Ô kết thúc điền dữ liệu
            Microsoft.Office.Interop.Excel.Range c2 = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[rowEnd, columnEnd];
            // Lấy về vùng điền dữ liệu
            Microsoft.Office.Interop.Excel.Range range = worksheet.get_Range(c1, c2);

            //Điền dữ liệu vào vùng đã thiết lập
            range.Value2 = arr;
            //range.Columns.AutoFit();
            range.Borders.LineStyle = Microsoft.Office.Interop.Excel.Constants.xlSolid;
            //theWorkbook=theWorkbook.Open("C:\\Huynh\\test.xlsx");
            //worksheet = ("sheet1");
            //range.Activate();
            object misValue = System.Reflection.Missing.Value;
            theWorkbook.Close(true, misValue, misValue);
            ExcelObj.Quit();
        }
        private void ExportExcel_LoiTuan(DataTable dt, string sheetName)
        {
            Microsoft.Office.Interop.Excel.Application ExcelObj = new Microsoft.Office.Interop.Excel.Application();
            ExcelObj.Visible = true;
            Microsoft.Office.Interop.Excel.Workbook theWorkbook = ExcelObj.Workbooks.Open(@"C:\\Huynh\\TKLoiTuan.xlsx", 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, false, Microsoft.Office.Interop.Excel.XlCorruptLoad.xlNormalLoad);
            Microsoft.Office.Interop.Excel.Sheets sheet = theWorkbook.Worksheets;
            Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)sheet.get_Item(1);
            //worksheet.Activate();
            object[,] arr = new object[dt.Columns.Count, dt.Rows.Count];
            //Chuyển dữ liệu từ DataTable vào mảng đối tượng
            for (int c = 0; c < dt.Columns.Count; c++)
            {
                //DataColumn dr = dt.Columns[r];
                for (int r = 0; r < dt.Rows.Count; r++)
                {
                    DataRow dr = dt.Rows[r];
                    arr[c, r] = dr[c];
                }
            }

            //Thiết lập vùng điền dữ liệu
            int rowStart = 6;
            int columnStart = 2;

            //int rowEnd = rowStart + dt.Rows.Count - 1;
            int rowEnd = rowStart + dt.Columns.Count - 1;

            //int columnEnd = dt.Columns.Count;
            int columnEnd = dt.Rows.Count - 1 + columnStart;

            // Ô bắt đầu điền dữ liệu
            Microsoft.Office.Interop.Excel.Range c1 = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[rowStart, columnStart];
            // Ô kết thúc điền dữ liệu
            Microsoft.Office.Interop.Excel.Range c2 = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[rowEnd, columnEnd];
            // Lấy về vùng điền dữ liệu
            Microsoft.Office.Interop.Excel.Range range = worksheet.get_Range(c1, c2);

            //Điền dữ liệu vào vùng đã thiết lập
            range.Value2 = arr;
            //range.Columns.AutoFit();
            range.Borders.LineStyle = Microsoft.Office.Interop.Excel.Constants.xlSolid;
            //theWorkbook=theWorkbook.Open("C:\\Huynh\\test.xlsx");
            //worksheet = ("sheet1");
            //range.Activate();
            object misValue = System.Reflection.Missing.Value;
            theWorkbook.Close(true, misValue, misValue);
            ExcelObj.Quit();
        }
        private void ExportExcel_LoiThang(DataTable dt, string sheetName)
        {
            Microsoft.Office.Interop.Excel.Application ExcelObj = new Microsoft.Office.Interop.Excel.Application();
            ExcelObj.Visible = true;
            Microsoft.Office.Interop.Excel.Workbook theWorkbook = ExcelObj.Workbooks.Open(@"C:\\Huynh\\TKLoiThang.xlsx", 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, false, Microsoft.Office.Interop.Excel.XlCorruptLoad.xlNormalLoad);
            Microsoft.Office.Interop.Excel.Sheets sheet = theWorkbook.Worksheets;
            Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)sheet.get_Item(1);
            //worksheet.Activate();
            object[,] arr = new object[dt.Columns.Count, dt.Rows.Count];
            //Chuyển dữ liệu từ DataTable vào mảng đối tượng
            for (int c = 0; c < dt.Columns.Count; c++)
            {
                //DataColumn dr = dt.Columns[r];
                for (int r = 0; r < dt.Rows.Count; r++)
                {
                    DataRow dr = dt.Rows[r];
                    arr[c, r] = dr[c];
                }
            }

            //Thiết lập vùng điền dữ liệu
            int rowStart = 6;
            int columnStart = 2;

            //int rowEnd = rowStart + dt.Rows.Count - 1;
            int rowEnd = rowStart + dt.Columns.Count - 1;

            //int columnEnd = dt.Columns.Count;
            int columnEnd = dt.Rows.Count - 1 + columnStart;

            // Ô bắt đầu điền dữ liệu
            Microsoft.Office.Interop.Excel.Range c1 = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[rowStart, columnStart];
            // Ô kết thúc điền dữ liệu
            Microsoft.Office.Interop.Excel.Range c2 = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[rowEnd, columnEnd];
            // Lấy về vùng điền dữ liệu
            Microsoft.Office.Interop.Excel.Range range = worksheet.get_Range(c1, c2);

            //Điền dữ liệu vào vùng đã thiết lập
            range.Value2 = arr;
            //range.Columns.AutoFit();
            range.Borders.LineStyle = Microsoft.Office.Interop.Excel.Constants.xlSolid;
            //theWorkbook=theWorkbook.Open("C:\\Huynh\\test.xlsx");
            //worksheet = ("sheet1");
            //range.Activate();
            object misValue = System.Reflection.Missing.Value;
            theWorkbook.Close(true, misValue, misValue);
            ExcelObj.Quit();
        }
        
        private void Search()
        {
            try
            {
                string select = null;
                //private string 
                DataSet ds = new DataSet();
                Process prs = new Process();
                #region Thong Ke Theo Ngay
                if (txt_dateend.Text != "" && txt_datestart.Text != "")
                {
                    Ngay_Start = txt_datestart.Text.Trim();
                    Ngay_End = txt_dateend.Text.Trim();
                    string[] temp = Ngay_Start.Split('/');
                    Ngay_Start = temp[1] + "/" + temp[0] + "/" + temp[2];
                    string[] temp2 = Ngay_End.Split('/');
                    Ngay_End = temp2[1] + "/" + temp2[0] + "/" + temp2[2];
                    // string select_Nhom = @"select * from dbo.select_TKNhomLoi";
                    #region Thong Ke Theo Chot KV2 va KV4
                    if (cb_Chot.Text == "KV2" || cb_Chot.Text == "KV4")
                    {
                        if (rd_LoiTongThe.Checked == true)
                        {
                            string select_CT = @"select * from select_THLoi";
                            if (cb_MaSP.Text != "")
                            {
                                MaSP1 = prs.substring_left(cb_MaSP.Text, child);

                                if (MaSP1 != "" && cb_Chot.Text != "")
                                {
                                    select = select_CT + " where Ngay>='" + Ngay_Start + " 00:00:00' and Ngay<='" + Ngay_End + " 23:59:59' and TenSP='" + MaSP1 + "' and Chot='" + cb_Chot.Text + "' order by Ngay";
                                }
                            }
                            if (cb_MaSP.Text == "" && cb_Chot.Text != "")
                            {
                                select = select_CT + " where Ngay>='" + Ngay_Start + " 00:00:00' and Ngay<='" + Ngay_End + " 23:59:59'and Chot='" + cb_Chot.Text + "' order by Ngay";
                            }

                        }
                        //dataGridView1.DataSource = ds.Tables[0];

                        if (rd_ThoiGian.Checked == true)
                        {
                            string select_ThoiGian = @"select * from dbo.report_TGNhomLoi";
                            if (cb_ChiTiet.Text == "")
                            {

                                if (cb_Chot.Text != "")
                                    select = select_ThoiGian + " where Ngay>='" + Ngay_Start + " 00:00:00' and Ngay<='" + Ngay_End + " 23:59:59' and Chot='" + cb_Chot.Text + "' order by Ngay";
                            }
                            if (cb_ChiTiet.Text != "")
                            {
                                select_ThoiGian = @"select * from dbo.select_THLoi";
                                MaChiTiet1 = prs.substring_right(cb_ChiTiet.Text, child);
                                if (MaChiTiet1 == "")
                                    MessageBox.Show("Thông Tin Ko Chính Xác");

                                if (MaChiTiet1 != "" && cb_Chot.Text != "")
                                    select = select_ThoiGian + " where Ngay>='" + Ngay_Start + " 00:00:00' and Ngay<='" + Ngay_End + " 23:59:59' and MaCT='" + MaChiTiet1 + "' and Chot='" + cb_Chot.Text + "' order by Ngay";
                            }
                            string TLKoDat_CT = @"select ChiTietSP.TenSP,ChiTietSP.TenChiTiet,convert(varchar(4),round(qry1.TLKoDat,3)*100)+'%' as TLKoDat 
                                                from (select TK_ChonMau.MaCT,sum(TK_ChonMau.SLKoDat)/sum(TK_ChonMau.SLTTe) as TLKoDat from dbo.TK_ChonMau where Chot='"+cb_Chot.Text+"' and Ngay>='"+Ngay_Start+" 00:00:00' and Ngay<='"+Ngay_End+" 23:59:59' group by TK_ChonMau.MaCT) as qry1 inner join ChiTietSP on qry1.MaCT=ChiTietSP.MaChiTiet order by qry1.TLKoDat Desc";
                            DataSet ds1 = new DataSet();
                            ds1 = prs.dts(TLKoDat_CT);
                            dataGridView1.DataSource = ds1.Tables[0];
                        }
                        if (rd_NhomLoi.Checked == true)
                        {
                            string select_NhomLoi = "select * from dbo.select_THLoi ";
                            if (cb_MaSP.Text != "")
                            {
                                MaSP1 = prs.substring_left(cb_MaSP.Text, child);

                                if (MaSP1 != "" && cb_Chot.Text != ""&&txt_Lannhung.Text=="")
                                {
                                    select = select_NhomLoi + " where Ngay>='" + Ngay_Start + " 00:00:00' and Ngay<='" + Ngay_End + " 23:59:59' and TenSP='" + MaSP1 + "' and Chot='" + cb_Chot.Text + "' order by Ngay";
                                }
                                if (MaSP1 != "" && cb_Chot.Text != "" && txt_Lannhung.Text != "")
                                {
                                    select = select_NhomLoi + " where Ngay>='" + Ngay_Start + " 00:00:00' and Ngay<='" + Ngay_End + " 23:59:59' and TenSP='" + MaSP1 + "' and Chot='" + cb_Chot.Text + "' and NguoiLayMau like '%"+txt_Lannhung.Text+"%' order by Ngay";
                                }
                            }
                            if (cb_MaSP.Text == "" && cb_Chot.Text != ""&&txt_Lannhung.Text=="")
                            {
                                select = select_NhomLoi + " where Ngay>='" + Ngay_Start + " 00:00:00' and Ngay<='" + Ngay_End + " 23:59:59'and Chot='" + cb_Chot.Text + "'order by Ngay";
                            }
                            if (cb_MaSP.Text == "" && cb_Chot.Text != "" && txt_Lannhung.Text != "")
                            {
                                select = select_NhomLoi + " where Ngay>='" + Ngay_Start + " 00:00:00' and Ngay<='" + Ngay_End + " 23:59:59'and Chot='" + cb_Chot.Text + "' and NguoiLayMau like '%" + txt_Lannhung.Text + "%' order by Ngay";
                            }
                        }
                        if (rd_Loi.Checked == true)
                        {

                            select = @"SELECT    dbo.select_TKChiTietLoi.Ngay, dbo.ChiTietSP.TenSP,dbo.ChiTietSP.TenChiTiet,
                            dbo.select_TKChiTietLoi.SLTTe, dbo.select_TKChiTietLoi.SLMau, dbo.select_TKChiTietLoi.SLKoDat, dbo.select_TKChiTietLoi.TLKoDat,";
                            string MaLoi = prs.substring_right(cb_LoiRp.Text, child);

                            if (MaLoi == "01") select += @"dbo.select_TKChiTietLoi.BeCanh,convert(varchar(4),round(dbo.select_TKChiTietLoi.TLBeCanh*100,1)) +'%' as TL";
                            if (MaLoi == "02") select += @"dbo.select_TKChiTietLoi.DuongCua_Canroulo,convert(varchar(4),round(dbo.select_TKChiTietLoi.TLDuongCua_Canroulo*100,1)) +'%' as TL";
                            if (MaLoi == "03") select += @"dbo.select_TKChiTietLoi.LocXuoc,convert(varchar(4),round(dbo.select_TKChiTietLoi.TLLocXuoc*100,1)) +'%' as TL";
                            if (MaLoi == "04") select += @"dbo.select_TKChiTietLoi.Nut_TetDau,convert(varchar(4),round(dbo.select_TKChiTietLoi.TLNut_TetDau*100,1)) +'%' as TL";
                            if (MaLoi == "05") select += @"dbo.select_TKChiTietLoi.MeDauNan,convert(varchar(4),round(dbo.select_TKChiTietLoi.TLMeDauNan*100,1)) +'%' as TL";
                            if (MaLoi == "06") select += @"dbo.select_TKChiTietLoi.Lem,convert(varchar(4),round(dbo.select_TKChiTietLoi.TLLem*100,1)) +'%' as TL";
                            if (MaLoi == "07") select += @"dbo.select_TKChiTietLoi.DauNanSanSui,convert(varchar(4),round(dbo.select_TKChiTietLoi.TLDauNanSanSui*100,1)) +'%' as TL";
                            if (MaLoi == "08") select += @"dbo.select_TKChiTietLoi.ChaNhamKoSach,convert(varchar(4),round(dbo.select_TKChiTietLoi.TLChaNhamKoSach*100,1)) +'%' as TL";
                            if (MaLoi == "09") select += @"dbo.select_TKChiTietLoi.TramTretKoDay,convert(varchar(4),round(dbo.select_TKChiTietLoi.TLTramTretKoDay*100,1)) +'%' as TL";
                            if (MaLoi == "10") select += @"dbo.select_TKChiTietLoi.DinhViSaiKT,convert(varchar(4),round(dbo.select_TKChiTietLoi.TLDinhViSaiKT*100,1)) +'%' as TL";
                            if (MaLoi == "11") select += @"dbo.select_TKChiTietLoi.DuongVatKoDat,convert(varchar(4),round(dbo.select_TKChiTietLoi.TLDuongVatKoDat*100,1)) +'%' as TL";
                            if (MaLoi == "12") select += @"dbo.select_TKChiTietLoi.DuongGhep,convert(varchar(4),round(dbo.select_TKChiTietLoi.TLDuongGhep*100,1)) +'%' as TL";
                            if (MaLoi == "13") select += @"dbo.select_TKChiTietLoi.GoKT,convert(varchar(4),round(dbo.select_TKChiTietLoi.TLGoKT*100,1)) +'%' as TL";
                            if (MaLoi == "14") select += @"dbo.select_TKChiTietLoi.KeoRapDinh,convert(varchar(4),round(dbo.select_TKChiTietLoi.TLKeoRapDinh*100,1)) +'%' as TL";
                            if (MaLoi == "15") select += @"dbo.select_TKChiTietLoi.SietOcKoDat,convert(varchar(4),round(dbo.select_TKChiTietLoi.TLSietOcKoDat*100,1)) +'%' as TL";
                            if (MaLoi == "16") select += @"dbo.select_TKChiTietLoi.NutNhuaKoSat,convert(varchar(4),round(dbo.select_TKChiTietLoi.TLNutNhuaKoSat*100,1)) +'%' as TL";
                            if (MaLoi == "17") select += @"dbo.select_TKChiTietLoi.CanMop,convert(varchar(4),round(dbo.select_TKChiTietLoi.TLCanMop*100,1)) +'%' as TL";
                            if (MaLoi == "18") select += @"dbo.select_TKChiTietLoi.KheNan,convert(varchar(4),round(dbo.select_TKChiTietLoi.TLKheNan*100,1)) +'%' as TL";
                            if (MaLoi == "19") select += @"dbo.select_TKChiTietLoi.ChayDau,convert(varchar(4),round(dbo.select_TKChiTietLoi.TLChayDau*100,1)) +'%' as TL";
                            if (MaLoi == "20") select += @"dbo.select_TKChiTietLoi.TrayDau,convert(varchar(4),round(dbo.select_TKChiTietLoi.TLTrayDau*100,1)) +'%' as TL";
                            if (MaLoi == "21") select += @"dbo.select_TKChiTietLoi.ThieuDau,convert(varchar(4),round(dbo.select_TKChiTietLoi.TLThieuDau*100,1)) +'%' as TL";
                            if (MaLoi == "22") select += @"dbo.select_TKChiTietLoi.CanDau,convert(varchar(4),round(dbo.select_TKChiTietLoi.TLCanDau*100,1)) +'%' as TL";
                            if (MaLoi == "23") select += @"dbo.select_TKChiTietLoi.MauKoDeu,convert(varchar(4),round(dbo.select_TKChiTietLoi.TLMauKoDeu*100,1)) +'%' as TL";
                            if (MaLoi == "24") select += @"dbo.select_TKChiTietLoi.DuongGhepHo,convert(varchar(4),round(dbo.select_TKChiTietLoi.TLDuongGhepHo*100,1)) +'%' as TL";
                            if (MaLoi == "25") select += @"dbo.select_TKChiTietLoi.MatKoDung,convert(varchar(4),round(dbo.select_TKChiTietLoi.TLMatKoDung*100,1)) +'%' as TL";
                            if (MaLoi == "26") select += @"dbo.select_TKChiTietLoi.CongVan,convert(varchar(4),round(dbo.select_TKChiTietLoi.TLCongVan*100,1)) +'%' as TL";
                            if (MaLoi == "27") select += @"dbo.select_TKChiTietLoi.DuongRuot,convert(varchar(4),round(dbo.select_TKChiTietLoi.TLDuongRuot*100,1)) +'%' as TL";
                            if (MaLoi == "28") select += @"dbo.select_TKChiTietLoi.MucMoc,convert(varchar(4),round(dbo.select_TKChiTietLoi.TLMucMoc*100,1)) +'%' as TL";
                            if (MaLoi == "29") select += @"dbo.select_TKChiTietLoi.Giac,convert(varchar(4),round(dbo.select_TKChiTietLoi.TLGiac*100,1)) +'%' as TL";
                            if (MaLoi == "30") select += @"dbo.select_TKChiTietLoi.KeoGhepDu,convert(varchar(4),round(dbo.select_TKChiTietLoi.TLKeoGhepDu*100,1)) +'%' as TL";
                            if (MaLoi == "31") select += @"dbo.select_TKChiTietLoi.ThieuQC,convert(varchar(4),round(dbo.select_TKChiTietLoi.TLThieuQC*100,1)) +'%' as TL";
                            if (MaLoi == "32") select += @"dbo.select_TKChiTietLoi.DoAm,convert(varchar(4),round(dbo.select_TKChiTietLoi.TLDoAm*100,1)) +'%' as TL";
                            if (MaLoi == "33") select += @"dbo.select_TKChiTietLoi.MatSongKoDat,convert(varchar(4),round(dbo.select_TKChiTietLoi.TLMatSongKoDat*100,1)) +'%' as TL";
                            if (MaLoi == "34") select += @"dbo.select_TKChiTietLoi.DinhViSaiMat,convert(varchar(4),round(dbo.select_TKChiTietLoi.TLDinhViSaiMat*100,1)) +'%' as TL";
                            if (MaLoi == "35") select += @"dbo.select_TKChiTietLoi.DuongVatBoCanh,convert(varchar(4),round(dbo.select_TKChiTietLoi.TLDuongVatBoCanh*100,1)) +'%' as TL";
                            if (MaLoi == "36") select += @"dbo.select_TKChiTietLoi.HoMong,convert(varchar(4),round(dbo.select_TKChiTietLoi.TLHoMong*100,1)) +'%' as TL";
                            if (MaLoi == "37") select += @"dbo.select_TKChiTietLoi.NanSaiMat,convert(varchar(4),round(dbo.select_TKChiTietLoi.TLNanSaiMat*100,1)) +'%' as TL";
                            if (MaLoi == "38") select += @"dbo.select_TKChiTietLoi.NanSaiTC,convert(varchar(4),round(dbo.select_TKChiTietLoi.TLNanSaiTC*100,1)) +'%' as TL";
                            if (MaLoi == "39") select += @"dbo.select_TKChiTietLoi.CaoTet,convert(varchar(4),round(dbo.select_TKChiTietLoi.TLCaoTet*100,1)) +'%' as TL";
                            if (MaLoi == "40") select += @"dbo.select_TKChiTietLoi.NanCaoThap,convert(varchar(4),round(dbo.select_TKChiTietLoi.TLNanCaoThap*100,1)) +'%' as TL";
                            if (MaLoi == "41") select += @"dbo.select_TKChiTietLoi.BaChan,convert(varchar(4),round(dbo.select_TKChiTietLoi.TLBaChan*100,1)) +'%' as TL";
                            if (MaLoi == "42") select += @"dbo.select_TKChiTietLoi.TanKoDat,convert(varchar(4),round(dbo.select_TKChiTietLoi.TLTanKoDat*100,1)) +'%' as TL";
                            if (MaLoi == "43") select += @"dbo.select_TKChiTietLoi.SaiMau,convert(varchar(4),round(dbo.select_TKChiTietLoi.TLSaiMau*100,1)) +'%' as TL";
                            if (MaLoi == "44") select += @"dbo.select_TKChiTietLoi.DongDau,convert(varchar(4),round(dbo.select_TKChiTietLoi.TLDongDau*100,1)) +'%' as TL";
                            if (MaLoi == "45") select += @"dbo.select_TKChiTietLoi.LemDau,convert(varchar(4),round(dbo.select_TKChiTietLoi.TLLemDau*100,1)) +'%' as TL";
                            if (MaLoi == "46") select += @"dbo.select_TKChiTietLoi.RangNut,convert(varchar(4),round(dbo.select_TKChiTietLoi.TLRangNut*100,1)) +'%' as TL";
                            if (MaLoi == "47") select += @"dbo.select_TKChiTietLoi.Khac,convert(varchar(4),round(dbo.select_TKChiTietLoi.TLKhac*100,1)) +'%' as TL";

                            select += @" from dbo.select_TKChiTietLoi INNER JOIN
                                        dbo.ChiTietSP ON dbo.select_TKChiTietLoi.MaCT = dbo.ChiTietSP.MaChiTiet where Ngay>='" + Ngay_Start + " 00:00:00' and Ngay<='" + Ngay_End + " 23:59:59' and Chot='" + cb_Chot.Text + "' order by Ngay,TL Desc";

                        }

                        //MessageBox.Show(select);
                        ds = prs.dts(select);
                        //MessageBox.Show(ds.Tables[0].Rows.Count.ToString());
                        if (ds.Tables[0].Rows.Count > 0)
                        {
                            if (rd_LoiTongThe.Checked == true)
                            {
                                ExportExcel(ds.Tables[0], "Test");
                                ds.Clear();
                            }
                            if (rd_NhomLoi.Checked == true || rd_ThoiGian.Checked == true)
                            {
                                ExportExcel_NhomLoi(ds.Tables[0], "NhomLoi");
                                ds.Clear();
                            }
                            //dataGridView1.DataSource = ds.Tables[0];
                            if (rd_Loi.Checked == true)
                            {
                                dataGridView1.DataSource = ds.Tables[0];
                                //MessageBox.Show(dataGridView1.Rows.Count.ToString());

                            }

                        }
                        else
                            MessageBox.Show("Không có dữ liệu");
                        //ds.Clear();

                    }
                    #endregion
                    #region Thong Ke Theo AQL
                    if (cb_Chot.Text == "KV1" || cb_Chot.Text == "KV2AQL" || cb_Chot.Text == "KV5_DG" || cb_Chot.Text == "KV5_NK"||cb_Chot.Text=="KV3")
                    {
                        if (rd_LoiTongThe.Checked == true)
                        {
                            string select_CT = @"select * from select_THLoiNK";
                            if (cb_MaSP.Text != "")
                            {
                                MaSP1 = prs.substring_left(cb_MaSP.Text, child);

                                if (MaSP1 != "" && cb_Chot.Text != "")
                                {
                                    select = select_CT + " where Ngay>='" + Ngay_Start + " 00:00:00' and Ngay<='" + Ngay_End + " 23:59:59' and TenSP='" + MaSP1 + "' and Chot='" + cb_Chot.Text + "' order by Ngay";
                                }
                            }
                            if (cb_MaSP.Text == "" && cb_Chot.Text != "")
                            {
                                select = select_CT + " where Ngay>='" + Ngay_Start + " 00:00:00' and Ngay<='" + Ngay_End + " 23:59:59'and Chot='" + cb_Chot.Text + "' order by Ngay";
                            }
                        }

                        //dataGridView1.DataSource = ds.Tables[0];

                        if (rd_ThoiGian.Checked == true)
                        {
                            string select_ThoiGian = @"select * from dbo.report_TGNhomLoiNK";
                            if (cb_ChiTiet.Text == "")
                            {

                                if (cb_Chot.Text != "")
                                    select = select_ThoiGian + " where Ngay>='" + Ngay_Start + " 00:00:00' and Ngay<='" + Ngay_End + " 23:59:59' and Chot='" + cb_Chot.Text + "' order by Ngay";
                            }
                            if (cb_ChiTiet.Text != "")
                            {
                                select_ThoiGian = @"select * from select_THLoiNK";
                                MaChiTiet1 = prs.substring_right(cb_ChiTiet.Text, child);
                                if (MaChiTiet1 == "")
                                    MessageBox.Show("Thông Tin Ko Chính Xác");

                                if (MaChiTiet1 != "" && cb_Chot.Text != "")
                                    select = select_ThoiGian + " where Ngay>='" + Ngay_Start + " 00:00:00' and Ngay<='" + Ngay_End + " 23:59:59' and MaChiTiet='" + MaChiTiet1 + "' and Chot='" + cb_Chot.Text + "' order by Ngay";
                            }
                        }
                        if (rd_NhomLoi.Checked == true)
                        {
                            string select_NhomLoi = "select * from select_THLoiNK ";
                            if (cb_MaSP.Text != "")
                            {
                                MaSP1 = prs.substring_left(cb_MaSP.Text, child);

                                if (MaSP1 != "" && cb_Chot.Text != "")
                                {
                                    select = select_NhomLoi + "where Ngay>='" + Ngay_Start + " 00:00:00' and Ngay<='" + Ngay_End + " 23:59:59' and TenSP='" + MaSP1 + "' and Chot='" + cb_Chot.Text + "' order by Ngay";
                                }
                            }
                            if (cb_MaSP.Text == "" && cb_Chot.Text != "")
                            {
                                select = select_NhomLoi + " where Ngay>='" + Ngay_Start + " 00:00:00' and Ngay<='" + Ngay_End + " 23:59:59'and Chot='" + cb_Chot.Text + "'order by Ngay";
                            }
                        }
                        if (rd_Loi.Checked == true)
                        {
                            select = @"SELECT    dbo.select_TKChiTietLoiNK.Ngay, dbo.ChiTietSP.TenSP,dbo.ChiTietSP.TenChiTiet,
                            dbo.select_TKChiTietLoiNK.SLTTe, dbo.select_TKChiTietLoiNK.SLMau, dbo.select_TKChiTietLoiNK.SLKoDat, dbo.select_TKChiTietLoiNK.TLKoDat,";
                            string MaLoi = prs.substring_right(cb_LoiRp.Text, child);
                            if (MaLoi == "01") select += @"dbo.select_TKChiTietLoiNK.BeCanh,dbo.select_TKChiTietLoiNK.TLBeCanh as TL";
                            if (MaLoi == "02") select += @"dbo.select_TKChiTietLoiNK.DuongCua_Canroulo,dbo.select_TKChiTietLoiNK.TLDuongCua_Canroulo as TL";
                            if (MaLoi == "03") select += @"dbo.select_TKChiTietLoiNK.LocXuoc,dbo.select_TKChiTietLoiNK.TLLocXuoc as TL";
                            if (MaLoi == "04") select += @"dbo.select_TKChiTietLoiNK.Nut_TetDau,dbo.select_TKChiTietLoiNK.TLNut_TetDau as TL";
                            if (MaLoi == "05") select += @"dbo.select_TKChiTietLoiNK.MeDauNan,dbo.select_TKChiTietLoiNK.TLMeDauNan as TL";
                            if (MaLoi == "06") select += @"dbo.select_TKChiTietLoiNK.Lem,dbo.select_TKChiTietLoiNK.TLLem as TL";
                            if (MaLoi == "07") select += @"dbo.select_TKChiTietLoiNK.DauNanSanSui,dbo.select_TKChiTietLoiNK.TLDauNanSanSui as TL";
                            if (MaLoi == "08") select += @"dbo.select_TKChiTietLoiNK.ChaNhamKoSach,dbo.select_TKChiTietLoiNK.TLChaNhamKoSach as TL";
                            if (MaLoi == "09") select += @"dbo.select_TKChiTietLoiNK.TramTretKoDay,dbo.select_TKChiTietLoiNK.TLTramTretKoDay as TL";
                            if (MaLoi == "10") select += @"dbo.select_TKChiTietLoiNK.DinhViSaiKT,dbo.select_TKChiTietLoiNK.TLDinhViSaiKT as TL";
                            if (MaLoi == "11") select += @"dbo.select_TKChiTietLoiNK.DuongVatKoDat,dbo.select_TKChiTietLoiNK.TLDuongVatKoDat as TL";
                            if (MaLoi == "12") select += @"dbo.select_TKChiTietLoiNK.DuongGhep,dbo.select_TKChiTietLoiNK.TLDuongGhep as TL";
                            if (MaLoi == "13") select += @"dbo.select_TKChiTietLoiNK.GoKT,dbo.select_TKChiTietLoiNK.TLGoKT as TL";
                            if (MaLoi == "14") select += @"dbo.select_TKChiTietLoiNK.KeoRapDinh,dbo.select_TKChiTietLoiNK.TLKeoRapDinh as TL";
                            if (MaLoi == "15") select += @"dbo.select_TKChiTietLoiNK.SietOcKoDat,dbo.select_TKChiTietLoiNK.TLSietOcKoDat as TL";
                            if (MaLoi == "16") select += @"dbo.select_TKChiTietLoiNK.NutNhuaKoSat,dbo.select_TKChiTietLoiNK.TLNutNhuaKoSat as TL";
                            if (MaLoi == "17") select += @"dbo.select_TKChiTietLoiNK.CanMop,dbo.select_TKChiTietLoiNK.TLCanMop as TL";
                            if (MaLoi == "18") select += @"dbo.select_TKChiTietLoiNK.KheNan,dbo.select_TKChiTietLoiNK.TLKheNan as TL";
                            if (MaLoi == "19") select += @"dbo.select_TKChiTietLoiNK.ChayDau,dbo.select_TKChiTietLoiNK.TLChayDau as TL";
                            if (MaLoi == "20") select += @"dbo.select_TKChiTietLoiNK.TrayDau,dbo.select_TKChiTietLoiNK.TLTrayDau as TL";
                            if (MaLoi == "21") select += @"dbo.select_TKChiTietLoiNK.ThieuDau,dbo.select_TKChiTietLoiNK.TLThieuDau as TL";
                            if (MaLoi == "22") select += @"dbo.select_TKChiTietLoiNK.CanDau,dbo.select_TKChiTietLoiNK.TLCanDau as TL";
                            if (MaLoi == "23") select += @"dbo.select_TKChiTietLoiNK.MauKoDeu,dbo.select_TKChiTietLoiNK.TLMauKoDeu as TL";
                            if (MaLoi == "24") select += @"dbo.select_TKChiTietLoiNK.DuongGhepHo,dbo.select_TKChiTietLoiNK.TLDuongGhepHo as TL";
                            if (MaLoi == "25") select += @"dbo.select_TKChiTietLoiNK.MatKoDung,dbo.select_TKChiTietLoiNK.TLMatKoDung as TL";
                            if (MaLoi == "26") select += @"dbo.select_TKChiTietLoiNK.CongVan,dbo.select_TKChiTietLoiNK.TLCongVan as TL";
                            if (MaLoi == "27") select += @"dbo.select_TKChiTietLoiNK.DuongRuot,dbo.select_TKChiTietLoiNK.TLDuongRuot as TL";
                            if (MaLoi == "28") select += @"dbo.select_TKChiTietLoiNK.MucMoc,dbo.select_TKChiTietLoiNK.TLMucMoc as TL";
                            if (MaLoi == "29") select += @"dbo.select_TKChiTietLoiNK.Giac,dbo.select_TKChiTietLoiNK.TLGiac as TL";
                            if (MaLoi == "30") select += @"dbo.select_TKChiTietLoiNK.KeoGhepDu,dbo.select_TKChiTietLoiNK.TLKeoGhepDu as TL";
                            if (MaLoi == "31") select += @"dbo.select_TKChiTietLoiNK.ThieuQC,dbo.select_TKChiTietLoiNK.TLThieuQC as TL";
                            if (MaLoi == "32") select += @"dbo.select_TKChiTietLoiNK.DoAm,dbo.select_TKChiTietLoiNK.TLDoAm as TL";
                            if (MaLoi == "33") select += @"dbo.select_TKChiTietLoiNK.MatSongKoDat,dbo.select_TKChiTietLoiNK.TLMatSongKoDat as TL";
                            if (MaLoi == "34") select += @"dbo.select_TKChiTietLoiNK.DinhViSaiMat,dbo.select_TKChiTietLoiNK.TLDinhViSaiMat as TL";
                            if (MaLoi == "35") select += @"dbo.select_TKChiTietLoiNK.DuongVatBoCanh,dbo.select_TKChiTietLoiNK.TLDuongVatBoCanh as TL";
                            if (MaLoi == "36") select += @"dbo.select_TKChiTietLoiNK.HoMong,dbo.select_TKChiTietLoiNK.TLHoMong as TL";
                            if (MaLoi == "37") select += @"dbo.select_TKChiTietLoiNK.NanSaiMat,dbo.select_TKChiTietLoiNK.TLNanSaiMat as TL";
                            if (MaLoi == "38") select += @"dbo.select_TKChiTietLoiNK.NanSaiTC,dbo.select_TKChiTietLoiNK.TLNanSaiTC as TL";
                            if (MaLoi == "39") select += @"dbo.select_TKChiTietLoiNK.CaoTet,dbo.select_TKChiTietLoiNK.TLCaoTet as TL";
                            if (MaLoi == "40") select += @"dbo.select_TKChiTietLoiNK.NanCaoThap,dbo.select_TKChiTietLoiNK.TLNanCaoThap as TL";
                            if (MaLoi == "41") select += @"dbo.select_TKChiTietLoiNK.BaChan,dbo.select_TKChiTietLoiNK.TLBaChan as TL";
                            if (MaLoi == "42") select += @"dbo.select_TKChiTietLoiNK.TanKoDat,dbo.select_TKChiTietLoiNK.TLTanKoDat as TL";
                            if (MaLoi == "43") select += @"dbo.select_TKChiTietLoiNK.SaiMau,dbo.select_TKChiTietLoiNK.TLSaiMau as TL";
                            if (MaLoi == "44") select += @"dbo.select_TKChiTietLoiNK.DongDau,dbo.select_TKChiTietLoiNK.TLDongDau as TL";
                            if (MaLoi == "45") select += @"dbo.select_TKChiTietLoiNK.LemDau,dbo.select_TKChiTietLoiNK.TLLemDau as TL";
                            if (MaLoi == "46") select += @"dbo.select_TKChiTietLoiNK.RangNut,dbo.select_TKChiTietLoiNK.TLRangNut as TL";
                            if (MaLoi == "47") select += @"dbo.select_TKChiTietLoiNK.Khac,dbo.select_TKChiTietLoiNK.TLKhac as TL";
                            select += @" from dbo.select_TKChiTietLoiNK INNER JOIN
                                        dbo.ChiTietSP ON dbo.select_TKChiTietLoiNK.MaCT = dbo.ChiTietSP.MaChiTiet where Ngay>='" + Ngay_Start + " 00:00:00' and Ngay<='" + Ngay_End + " 23:59:59' and Chot='" + cb_Chot.Text + "' order by Ngay,TL Desc";
                        }
                        ds = prs.dts(select);
                        if (ds.Tables[0].Rows.Count > 0)
                        {
                            if (rd_LoiTongThe.Checked == true)
                            {
                                ExportExcel(ds.Tables[0], "Test");
                                ds.Clear();
                            }
                            if (rd_NhomLoi.Checked == true || rd_ThoiGian.Checked == true)
                            {
                                ExportExcel_NhomLoi(ds.Tables[0], "NhomLoi");
                                ds.Clear();
                            }
                            if (rd_Loi.Checked == true)
                            {
                                dataGridView1.DataSource = ds.Tables[0];
                            }
                            //dataGridView1.DataSource = ds.Tables[0];
                        }
                        else
                            MessageBox.Show("Không có dữ liệu");

                    }
                }
                #endregion
                #endregion
                #region Thong Ke Theo Tuan Va Thang
                else
                {
                    if (rd_TuanRp.Checked == true)
                    {
                        if (txt_wkstart.Text == "" || txt_wkend.Text == "" || txt_year.Text == "")
                            MessageBox.Show("Nhập đầy đủ Từ Tuần- Đến Tuần-Năm");
                        else
                        {
                            int Tuan_int = int.Parse(txt_wkstart.Text);
                            Ngay_Start = Tuan_int.ToString();
                            Tuan_int = int.Parse(txt_wkend.Text);
                            Ngay_End = Tuan_int.ToString();
                            int year = int.Parse(txt_year.Text);
                            //MessageBox.Show("aaaa");
                            if (cb_Chot.Text != "")
                            {
                                if (cb_MaSP.Text == "")
                                {
                                    
                                    select = @"select * from dbo.select_TKLoiTuanTH where Ngay>=" + Ngay_Start + " and Ngay<=" + Ngay_End + " and Nam=" + year + " and Chot='" + cb_Chot.Text+"'";
                                }
                                if (cb_MaSP.Text != "")
                                {
                                    if (cb_ChiTiet.Text == "")
                                    {
                                        MaSP1 = prs.substring_right(cb_MaSP.Text, child);
                                        select = @"select * from dbo.select_TKLoiTuanSP where Ngay>=" + Ngay_Start + " and Ngay<=" + Ngay_End + " and Nam=" + year + " and Chot='" + cb_Chot.Text+"' and MaSP=N'"+MaSP1+"'";
                                    }
                                    if (cb_ChiTiet.Text != "")
                                    {
                                        MaChiTiet1 = prs.substring_right(cb_ChiTiet.Text, child);
                                        select = @"select * from dbo.select_TKLoiTuanCT where Ngay>=" + Ngay_Start + " and Ngay<=" + Ngay_End + " and Nam=" + year + " and Chot='" + cb_Chot.Text + "' and MaCT=N'" + MaChiTiet1 + "'";
                                    }

                                }
                            }
                            
                            ds = prs.dts(select);
                            ExportExcel_LoiTuan(ds.Tables[0], "NhomLoi");
                            
                            ds.Clear();

                        }
                    }
                    if (rd_LoiThang.Checked == true)
                    {
                        if (txt_wkstart.Text == "" || txt_wkend.Text == "" || txt_year.Text == "")
                            MessageBox.Show("Nhập đầy đủ Từ Tháng- Đến Tháng-Năm");
                        else
                        {
                            int Tuan_int = int.Parse(txt_wkstart.Text);
                            Ngay_Start = Tuan_int.ToString();
                            Tuan_int = int.Parse(txt_wkend.Text);
                            Ngay_End = Tuan_int.ToString();
                            int year = int.Parse(txt_year.Text);
                            //MessageBox.Show("aaaa");
                            if (cb_Chot.Text != "")
                            {
                                if (cb_MaSP.Text == "")
                                {

                                    select = @"select * from dbo.select_TKLoiThangTH where Ngay>=" + Ngay_Start + " and Ngay<=" + Ngay_End + " and Nam=" + year + " and Chot='" + cb_Chot.Text + "'";
                                }
                                if (cb_MaSP.Text != "")
                                {
                                    if (cb_ChiTiet.Text == "")
                                    {
                                        MaSP1 = prs.substring_right(cb_MaSP.Text, child);
                                        select = @"select * from dbo.select_TKLoiThangSP where Ngay>=" + Ngay_Start + " and Ngay<=" + Ngay_End + " and Nam=" + year + " and Chot='" + cb_Chot.Text + "' and MaSP=N'" + MaSP1 + "'";
                                    }
                                    if (cb_ChiTiet.Text != "")
                                    {
                                        MaChiTiet1 = prs.substring_right(cb_ChiTiet.Text, child);
                                        select = @"select * from dbo.select_TKLoiThangCT where Ngay>=" + Ngay_Start + " and Ngay<=" + Ngay_End + " and Nam=" + year + " and Chot='" + cb_Chot.Text + "' and MaCT=N'" + MaChiTiet1 + "'";
                                    }

                                }
                            }

                            ds = prs.dts(select);
                            ExportExcel_LoiThang(ds.Tables[0], "NhomLoi");

                            ds.Clear();
                        }

                    }
                    #endregion
                    else
                      MessageBox.Show("Cho Biết Thời Gian Tìm Kiếm");
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("Có thể thông tin bạn nhập chưa chính xác \n" + ex.Message);
            }
            
            
        }

        private void bt_view_Click(object sender, EventArgs e)
        {
            //Search();
        }

        private void bt_ExportExcel_Click(object sender, EventArgs e)
        {
            Search();
        }

        private void FrTKReport_Load(object sender, EventArgs e)
        {
            combo_load();
            cb_Chot.SelectedIndex = 3;
        }

        private void cb_ChiTiet_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (rd_ThoiGian.Checked == false)
                MessageBox.Show("Chọn mục THỜI GIAN để xem theo chi tiết");
        }

        private void cb_MaSP_SelectedIndexChanged(object sender, EventArgs e)
        {
            Process prs = new Process();
            FormProcess Frp = new FormProcess();
            MaSP1 = prs.substring_right(cb_MaSP.Text, child);
            cb_ChiTiet.Items.Clear();
            string sql_load = @"select MaChiTiet,TenChiTiet from dbo.ChiTietSP where MaSP='" + MaSP1 + "'";
            Frp.cb1(cb_ChiTiet, sql_load, 1, 0);
        }

        private void rd_TuanRp_CheckedChanged(object sender, EventArgs e)
        {
            txt_dateend.Text = "";
            txt_datestart.Text = "";
            lb_wkbegin.Text = "Từ Tuần";
            lb_wkend.Text = "Tới Tuần";
        }

        private void rd_NhomLoi_CheckedChanged(object sender, EventArgs e)
        {
            txt_wkend.Text = "";
            txt_wkstart.Text = "";
            txt_year.Text = "";
        }

        private void rd_LoiThang_CheckedChanged(object sender, EventArgs e)
        {
            lb_wkbegin.Text = "Từ Tháng";
            lb_wkend.Text = "Đến Tháng";
            txt_dateend.Text = "";
            txt_datestart.Text = "";
        }

        private void rd_ThoiGian_CheckedChanged(object sender, EventArgs e)
        {
            txt_wkend.Text = "";
            txt_wkstart.Text = "";
        }

        #region ThongKeTonMatBang
        private void TKTonKV3()
        {
            Process prs = new Process();
            DataSet ds = new DataSet();
            string TonLR = null;
            try
            {
                if (rd_LapRap.Checked == true)
                {
                    TonLR = @"select ChiTietSP.MaChiTiet,ChiTietSP.TenSP,ChiTietSP.TenChiTiet,ChiTietSP.SoLuongCT,KV2.SLXP,qry1.SLNhapKTam,TK_HangLoai.SLLoai,isnull(KV2.SLXP,0)-(isnull(SLNhapKTam,0))-isnull(TK_HangLoai.SLLoai,0) as SLTonLR,qry1.MaCTlink from
                            (select KV2_LXP.MaChiTiet,sum(KV2_LXP.SLXP) as SLXP from dbo.KV2_LXP group by KV2_LXP.MaChiTiet) as KV2 left join
                            (select ChiTiet_KhuVuc.MaCT,(KTam.SLNhap*ChiTiet_KhuVuc.SLCT_QuyDoi) as SLNhapKTam,ChiTiet_KhuVuc.ID_link as MaCTlink from
                            (select KV4_KTam.MaCT,sum(isnull(KV4_KTam.SLNhap,0)) as SLNhap from dbo.KV4_KTam group by MaCT) as KTam
                            inner join ChiTiet_KhuVuc on KTam.MaCT=ChiTiet_KhuVuc.ID_link where ChiTiet_KhuVuc.KhuVuc='KV2') as qry1
                            on KV2.MaChiTiet=qry1.MaCT left join (select TK_HangLoai.MaCT,sum(SLLoai) as SLLoai from dbo.TK_HangLoai where KhuVuc='KV3' group by TK_HangLoai.MaCT) as TK_HangLoai
                            on TK_HangLoai.MaCT=KV2.MaChiTiet inner join ChiTietSP on ChiTietSP.MaChiTiet=KV2.MaChiTiet order by ChiTietSP.MaChiTiet";
                    ds = prs.dts(TonLR);
                    dataGridView2.DataSource = ds.Tables[0];
                }
                if (rd_TonKV4.Checked == true)
                {
                    TonLR = @"select ChiTietSP.MaChiTiet,ChiTietSP.TenChiTiet,KTam.SLXuatKTam,qry1.SoVe,KTam.SLXuatKTam-isnull(qry1.SoVe,0)-isnull(HangLoai.SLLoai,0) as SLTonKV4 from
                            (select dbo.KV4_KTam.MaCT,sum(isnull(KV4_KTam.SLXuat,0)) as SLXuatKTam from dbo.KV4_KTam group by dbo.KV4_KTam.MaCT) as KTam left join
                            (select ChiTiet_KhuVuc.MaCT,(ChiTiet_KhuVuc.SLCT_QuyDoi*KhoTP.SLNhapKTP) as SoVe from
                            (select dbo.KhoTP.MaSP,sum(isnull(dbo.KhoTP.SLNhap,0)) as SLNhapKTP from dbo.KhoTP group by dbo.KhoTP.MaSP) as KhoTP
                            inner join ChiTiet_KhuVuc on ChiTiet_KhuVuc.MaSP=KhoTP.MaSP where ChiTiet_KhuVuc.KhuVuc='KV3') as qry1
                            on qry1.MaCT=KTam.MaCT left join (select TK_HangLoai.MaCT,sum(isnull(TK_HangLoai.SLLoai,0)) as SLLoai from TK_HangLoai group by TK_HangLoai.MaCT) as HangLoai on HangLoai.MaCT=KTam.MaCT inner join ChiTietSP on ChiTietSP.MaChiTiet=KTam.MaCT";
                    ds = prs.dts(TonLR);
                    dataGridView2.DataSource = ds.Tables[0];
                } 
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }

           
        }
        private void bt_TonLR_Click(object sender, EventArgs e)
        {
            TKTonKV3();
        }


        #endregion

        private void bt_print_Click(object sender, EventArgs e)
        {
             Process prs = new Process();
            DataSet ds = new DataSet();
            string TonLR = null;
            try
            {
                if (rd_LapRap.Checked == true)
                {
                    TonLR = @"select ChiTietSP.MaChiTiet,ChiTietSP.TenSP,ChiTietSP.TenChiTiet,ChiTietSP.SoLuongCT,KV2.SLXP,qry1.SLNhapKTam as SLNhap,TK_HangLoai.SLLoai as SoLuong,isnull(KV2.SLXP,0)-(isnull(SLNhapKTam,0))-isnull(TK_HangLoai.SLLoai,0) as SLTon,qry1.MaCTlink from
                                (select KV2_LXP.MaChiTiet,sum(KV2_LXP.SLXP) as SLXP from dbo.KV2_LXP group by KV2_LXP.MaChiTiet) as KV2 left join
                                (select ChiTiet_KhuVuc.MaCT,(KTam.SLNhap*ChiTiet_KhuVuc.SLCT_QuyDoi) as SLNhapKTam,ChiTiet_KhuVuc.ID_link as MaCTlink from
                                (select KV4_KTam.MaCT,sum(isnull(KV4_KTam.SLNhap,0)) as SLNhap from dbo.KV4_KTam group by MaCT) as KTam
                                inner join ChiTiet_KhuVuc on KTam.MaCT=ChiTiet_KhuVuc.ID_link where ChiTiet_KhuVuc.KhuVuc='KV2') as qry1
                                on KV2.MaChiTiet=qry1.MaCT left join (select TK_HangLoai.MaCT,sum(SLLoai) as SLLoai from dbo.TK_HangLoai where KhuVuc='KV3' and BienPhap like N'Bỏ' group by TK_HangLoai.MaCT) as TK_HangLoai
                                on TK_HangLoai.MaCT=KV2.MaChiTiet inner join ChiTietSP on ChiTietSP.MaChiTiet=KV2.MaChiTiet order by ChiTietSP.MaChiTiet";
                    ds = prs.dts(TonLR);
                    Fr_Report FrR = new Fr_Report();
                    FrR.report_TonLR(ds);
                    FrR.Show();
                    ds.Clear();
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        

    }
}
